const express = require("express");
const pool = require("../pool.js");

const petsRouter = express.Router();
module.exports = petsRouter;
// 根据id查宠物
petsRouter.get("/find", (req, res,next) => {
  var petid = req.query.petid;
  var sql = `select * from pets where petid=?`;
  pool.query(sql, [petid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      res.send({ code: 200, msg: "ok", data: r });
    } else {
      res.send({ code: 201, msg: "null" });
    }
  });
});
// 根据种类查宠物
petsRouter.get("/findKind", (req, res,next) => {
  var petclass = req.query.petclass;
  var sql = `select * from pets where petclass=?`;
  pool.query(sql, [petclass], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) res.send({ code: 200, data: r });
    else res.send({ code: 201, msg: "无数据" });
  });
});
// 根据id查宠物价格
petsRouter.get("/findPrice", (req, res,next) => {
  var petid = req.query.petid;
  if (petid == "" || petid == undefined) {
    res.send({ code: 202, msg: "宠物id不可为空" });
    return;
  }
  var sql = `select petprice from pets where petid=?`;
  pool.query(sql, [petid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      let price = r[0].petprice;
      res.send({ code: 200, price });
    } else {
      res.send({ code: 201, msg: "找不到该宠物" });
    }
  });
});
// 查询整个表
petsRouter.get("/findAll", (req, res,next) => {
  var page = req.query.page || 1;
  const pagesize = 10;
  var offset = (page - 1) * pagesize;
  var sql = `select count(petid) as count from pets`;
  pool.query(sql, (err, r) => {
    if (err) {
      next(err);
      return;
    }
    let pagecount = Math.ceil(r[0].count / pagesize);
    var sql = `select * from pets limit ?,?`;
    pool.query(sql, [offset, pagesize], (err, r) => {
      if (err) {
        next(err);
        return;
      }
      res.send({ code: 200, msg: "ok", data: r, pagecount });
    });
  });
});
// 新增宠物
petsRouter.post("/insert", (req, res,next) => {
  var petclass = req.body.petclass;
  var petsex = req.body.petsex;
  var petage = req.body.petage;
  var petdesc = req.body.petdesc;
  var petimg = req.body.petimg;
  var petprice = req.body.petprice;
  var petstate = req.body.petstate;
  if (
    petclass == "" ||
    petclass == undefined ||
    petsex == "" ||
    petsex == undefined ||
    petage == "" ||
    petage == undefined ||
    petdesc == "" ||
    petdesc == undefined ||
    petimg == "" ||
    petimg == undefined ||
    petprice == "" ||
    petprice == undefined ||
    petstate == "" ||
    petstate == undefined
  ) {
    res.send({ code: 202, msg: "输入不可为空" });
    return;
  }
  var sql1 = `select kid from kind where kname=?`;
  pool.query(sql1, [petclass], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      var sql2 = `insert into pets values(null,?,?,?,?,?,?,?)`;
      pool.query(
        sql2,
        [petclass, petsex, petage, petdesc, petimg, petprice, petstate],
        (err, r) => {
          if (err) {
            next(err);
            return;
          }
          if (r.affectedRows != 0) {
            res.send({ code: 200, msg: "插入成功" });
          } else {
            res.send({ code: 203, msg: "插入失败" });
          }
        }
      );
    } else {
      res.send({ code: 201, msg: "宠物种类不在库内" });
    }
  });
});
// 删除宠物
petsRouter.delete("/delete", (req, res,next) => {
  var petid = req.query.petid;
  var sql = `delete from pets where petid=?`;
  pool.query(sql, [petid], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (!r.affectedRows) {
      res.send({ code: 201, msg: "删除失败" });
    } else {
      res.send({ code: 200, msg: "删除成功" });
    }
  });
});
// 修改宠物数据
petsRouter.put("/update", (req, res,next) => {
  var petid = req.body.petid;
  var petclass = req.body.petclass;
  var petsex = req.body.petsex;
  var petage = req.body.petage;
  var petdesc = req.body.petdesc;
  var petimg = req.body.petimg;
  var petprice = req.body.petprice;
  var petstate = req.body.petstate;
  if (
    petclass == "" ||
    petclass == undefined ||
    petsex == "" ||
    petsex == undefined ||
    petage == "" ||
    petage == undefined ||
    petdesc == "" ||
    petdesc == undefined ||
    petimg == "" ||
    petimg == undefined ||
    petprice == "" ||
    petprice == undefined ||
    petstate == "" ||
    petstate == undefined
  ) {
    res.send({ code: 202, msg: "输入不可为空" });
    return;
  }
  var sql = `select kid from kind where kname=?`;
  pool.query(sql, [petclass], (err, r) => {
    if (err) {
      next(err);
      return;
    }
    if (r.length != 0) {
      sql = `update pets set petclass=?,petsex=?,petage=?,petdesc=?,petimg=?,petprice=?,petstate=? where petid=?`;
      pool.query(
        sql,
        [petclass, petsex, petage, petdesc, petimg, petprice, petstate, petid],
        (err, r) => {
          if (err) {
            next(err);
            return;
          }
          if (r.affectedRows != 0) {
            res.send({ code: 200, msg: "修改成功" });
          } else {
            res.send({ code: 201, msg: "修改失败" });
          }
        }
      );
    } else {
      res.send({ code: 203, msg: "找不到此类宠物" });
    }
  });
});
